package com.cbw.dao.ImpI;

import com.cbw.bean.Courier;
import com.cbw.bean.ResultData;
import com.cbw.dao.BaseCourierDao;
import com.cbw.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class BaseCourierDaoMysql implements BaseCourierDao {
/*
* //不可以直接传data，我们的data是util包下的，此方法需要sql包下的data传入，需调用sql包data构造方法
            state.setDate(1,new java.sql.Date(date.getTime()));
* */
    private static final String SQL_CONSOLE="select "+
            "count(id) total_size,"+  //快递员总数
            "count(to_days(createTime)=to_days(now()) or null) day_num "+//今日新增:注册时间=今日时间
            "from courier";

    private static final String SQL_FIND_ALL_LIMIT ="select * from courier limit ?,?" ;
    private static final String SQL_FIND_ALL = "select * from courier";
    private static final String SQL_FIND_BY_PHONE ="select * from courier where phone=?" ;
    private static final String SQL_INSERT = "insert into courier(number,username,phone,cardId,password,sendNumber,createTime) values (?,?,?,?,?,0,now())";
    private static final String SQL_UPDATE = "update courier set username=?,phone=?,cardId=?,password=? where id=?";
    private static final String SQL_DELETE = "delete from courier where id=? ";
    private static final String SQL_TOTAL = "select count(id) total from courier";
    private static Connection conn=null;
    private static PreparedStatement pps=null;
    private static ResultSet resultSet=null;
    static int num=1000;

    /**
     * 查询数据库的全部快递员(总数和新增)、--控制台部分
     *
     * @return {size:总数,day:新增}
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> list = new ArrayList<>();
        //获取连接
        conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_CONSOLE);
            //查询
            resultSet = pps.executeQuery();
            //封装数据
            if (resultSet.next()){
                int total_size=resultSet.getInt("total_size");
                int day_num=resultSet.getInt("day_num");
                HashMap<String, Integer> map = new HashMap<>();
                map.put("total_size",total_size);
                map.put("day_num",day_num);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }

    /**
     * 分页查询快递员
     * select * from courier limit ?,?
     *select * from courier
     * @param limit      是否分页
     * @param offset     开始索引
     * @param pageNumber 每页记录数
     * @return
     */
    @Override
    public List<Courier> list(boolean limit, int offset, int pageNumber) {
        List<Courier>list=new ArrayList<>();;
        //获取连接
        conn = DruidUtil.getConnection();
        try {
            //预编译sql
            if (limit){
                pps = conn.prepareStatement(SQL_FIND_ALL_LIMIT);
                //填充参数
                pps.setInt(1,offset);
                pps.setInt(2,pageNumber);

            }else {
                pps = conn.prepareStatement(SQL_FIND_ALL);
            }
            //查询
            ResultSet resultSet = pps.executeQuery();
            while (resultSet.next()){
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setNumber(resultSet.getString("number"));
                courier.setUsername(resultSet.getString("username"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setCardId(resultSet.getString("cardId"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSendNumber(Integer.parseInt(resultSet.getString("sendNumber")));
                courier.setCreateTime(resultSet.getDate("createTime"));
                courier.setLasTime(resultSet.getDate("lasTime"));
                System.out.println("分页查询快递员"+courier);
                list.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        System.out.println("分页查询结果:"+list);
        return list;
    }

    /**
     * 通过手机查询快递员
     *select * from courier where phone=?
     * @param phone
     * @return
     */
    @Override
    public Courier findByPhone(String phone) {
        //获取连接
        conn = DruidUtil.getConnection();
        Courier courier= null;
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //填充参数
            pps.setString(1,phone);
            //查询
            ResultSet resultSet = pps.executeQuery();
            courier = null;
            if (resultSet.next()){
                courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setNumber(resultSet.getString("number"));
                courier.setPhone(resultSet.getString("phone"));
                courier.setUsername(resultSet.getString("username"));
                courier.setCardId(resultSet.getString("cardId"));
                courier.setPassword(resultSet.getString("password"));
                courier.setSendNumber(Integer.parseInt(resultSet.getString("sendNumber")));
                courier.setCreateTime(resultSet.getDate("createTime"));
                courier.setLasTime(resultSet.getDate("lasTime"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return courier;
    }

    /**
     * 新增快递员
     *insert into courier(number,username,phone,cardId,password,sendNumber,createTime) values (?,?,?,?,?,0,now())
     *新增快递员操作是管理员操作的，所以lasTime上次登录字段不应该设置值，给null,刚注册的快递员并没有登录，页面通过对lasTime判空显示"尚未登录"
     *  @param courier
     * @return
     */
    @Override
    public boolean insert(Courier courier) {
        num++;
        try {
            //获取连接
            conn = DruidUtil.getConnection();
            //预编译sql
            pps = conn.prepareStatement(SQL_INSERT);
            //填充参数
            pps.setString(1,num+"");
            pps.setString(2,courier.getUsername());
            pps.setString(3,courier.getPhone());
            pps.setString(4,courier.getCardId());
            pps.setString(5,courier.getPassword());
            //插入
            int i = pps.executeUpdate();
            if (i>0) return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    /**
     * 更新快递员
     *update courier set username=?,phone=?,cardId=?,password=? where id=?
     * @param courier
     * @return
     */
    @Override
    public boolean update(Courier courier) {
        //获取连接
         conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_UPDATE);
            //填充参数
            pps.setString(1,courier.getUsername());
            pps.setString(2,courier.getPhone());
            pps.setString(3,courier.getCardId());
            pps.setString(4,courier.getPassword());
            pps.setInt(5,courier.getId());
            //更新
            int i = pps.executeUpdate();
            if (i>0) return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    /**
     * 删除快递员
     *delete from courier where id=?
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        //获取连接
         conn = DruidUtil.getConnection();
        try {
            //预编译Sql
            pps = conn.prepareStatement(SQL_DELETE);
            //填充参数
            pps.setInt(1,id);
            //删除
            int i = pps.executeUpdate();
            if (i>0) return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    /**
     * select count(id) total from courier
     * @return
     */
    @Override
    public int total() {
        //获取连接
         conn = DruidUtil.getConnection();
         int total=0;
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_TOTAL);
            //查询
            resultSet = pps.executeQuery();
            if (resultSet.next()){
                total=resultSet.getInt("total");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return total;
    }
}
